﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using Utility.Helper;

namespace BLL
{
    public class QS_PLAN_DETAIL2_BLL : BaseBLL
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name="proId">项目id</param>
        /// <param name="type">类型：1 年资金,2 月资金,3 周资金</param>
        /// <returns></returns>
        public List<QS_PLAN_DETAIL2> FindRange3(int applyId, int proId, int type, string userId = "null")
        {
            if (userId == "null")
            {
                userId = base.User.USER_ID.ToString();
            }

            string sql = null;

            //查询周(汇总或成员)
            if (type == 3 || type == 5)
            {
                sql = @"
  SELECT
    T2.*,
    CASE
      WHEN T2.N_AMOUNT IS NULL THEN '0%'
      WHEN T2.N_AMOUNT =0 THEN '0%'
      WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
      WHEN T2.N_USEAMOUNT =0 THEN '0%'
      ELSE
         TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_AMOUNT*100,6))||'%'
    END AS N_GOL
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE
  WHERE
        T1.N_WEEKAPP_ID = :APP_ID";

                //若为周成员则添加一个条件
                if (type == 5)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }
            }

            //查询月(汇总或成员)
            if (type == 2 || type == 4)
            {
                sql = @"SELECT
  T1.*,
  T2.N_GOL
FROM
  (SELECT
    T2.*
  FROM QS_PLANMONTHAPPLY T1
  INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_MONTHAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE";

                //若为月成员则添加一个条件
                if (type == 4)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }

                sql += @" WHERE
    T1.N_MONTHAPP_ID = :APP_ID
  ) T1
FULL JOIN (
SELECT
  T1.*,
  CASE
      WHEN T1.N_AMOUNT IS NULL THEN '0%'
      WHEN T1.N_AMOUNT = 0 THEN '0%'
      WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
      WHEN T1.N_USEAMOUNT = 0 THEN '0%'
      ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
   END AS N_GOL
FROM
(
  SELECT
    T2.N_CUSTOMER_ID,
    T2.C_USER_TYPE,
    SUM(
      CASE
        WHEN T2.N_DOSAGE IS NULL THEN 0
        ELSE T2.N_DOSAGE
      END) AS N_DOSAGE,
    SUM(
      CASE
        WHEN T2.N_PRICE IS NULL THEN 0
        ELSE T2.N_PRICE
      END) AS N_PRICE,
    SUM(
      CASE
        WHEN T2.N_AMOUNT IS NULL THEN 0
        ELSE T2.N_AMOUNT
      END) AS N_AMOUNT,
    SUM(
      CASE
        WHEN T2.N_USEAMOUNT IS NULL THEN 0
        ELSE T2.N_USEAMOUNT
      END) AS N_USEAMOUNT
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
  INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
  WHERE
    T1.N_BUSINESS_STATE = 2 AND
    T1.N_MONTHAPP_ID = :APP_ID
  GROUP BY
    T2.N_CUSTOMER_ID,T2.C_USER_TYPE) T1) T2 ON T1.N_CUSTOMER_ID = T2.N_CUSTOMER_ID AND T1.C_USER_TYPE = T2.C_USER_TYPE";
            }

            List<Tuple<string, string, string>> parms = new List<Tuple<string, string, string>>();

            if (type == 4 || type == 5)
            {
                parms.Add(new Tuple<string, string, string>(":N_USERID", userId, "int"));
            }

            parms.Add(new Tuple<string, string, string>(":APP_ID", applyId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_TYPE", type.ToString(), "int"));

            var dt = Query(sql, parms);

            var entitys = new List<QS_PLAN_DETAIL2>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAIL2();
                entity.N_ID2 = decimal.Parse(row["N_ID2"].ToString());
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = decimal.Parse(row["N_TYPE"].ToString());

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = row["N_NUM"] == DBNull.Value ? 0 : decimal.Parse(row["N_NUM"].ToString());
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == DBNull.Value ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_USER_TYPE = row["C_USER_TYPE"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == DBNull.Value ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == DBNull.Value ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == DBNull.Value ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entity.N_AMT = row["N_USEAMOUNT"] == DBNull.Value ? 0 : decimal.Parse(row["N_USEAMOUNT"].ToString());
                entity.C_AMTPRO = row["N_GOL"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(月)
        /// </summary>
        public void GetMonthCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       N_CUSTOMER_ID,
       C_CUSTOMER_NAME,
       C_USER_TYPE,
       round(sum(N_DOSAGE),6) N_DOSAGE,
       round(avg(N_PRICE),6) N_PRICE,
       round(sum(N_AMOUNT),6) N_AMOUNT
  from QS_PLAN_DETAIL2 t
 WHERE t.n_type = 4
   and t.n_pro_id = " + proId + @"
   and t.n_planapp_id = " + applyId + @"
GROUP BY N_PRO_ID,N_TYPE,N_PLANAPP_ID,N_CUSTOMER_ID,C_CUSTOMER_NAME,C_USER_TYPE";

            var dt = Query(sql);
            decimal num = 0;
            var entitys = new List<QS_PLAN_DETAIL2>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;
                var entity = new QS_PLAN_DETAIL2();
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 2;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = num;
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == null ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_USER_TYPE = row["C_USER_TYPE"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == null ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == null ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == null ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = "";

                entity.C_REMARK = "";

                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                UpdateOrCreateRange3(applyId, proId, 2, entitys);
            }
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(周)
        /// </summary>
        public void GetWeekCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       N_CUSTOMER_ID,
       C_CUSTOMER_NAME,
       C_USER_TYPE,C_BANK,C_REMARK,
       round(sum(N_DOSAGE),6) N_DOSAGE,
       round(avg(N_PRICE),6) N_PRICE,
       round(sum(N_AMOUNT),6) N_AMOUNT
  from QS_PLAN_DETAIL2 t
 WHERE t.n_type = 5
   and t.n_pro_id = " + proId + @"
   and t.n_planapp_id = " + applyId + @"
GROUP BY N_PRO_ID,N_TYPE,N_PLANAPP_ID,N_CUSTOMER_ID,C_CUSTOMER_NAME,C_USER_TYPE,C_BANK,C_REMARK";

            var dt = Query(sql);
            decimal num = 0;
            var entitys = new List<QS_PLAN_DETAIL2>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;
                var entity = new QS_PLAN_DETAIL2();
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 3;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());

                entity.N_NUM = num;
                entity.N_CUSTOMER_ID = row["N_CUSTOMER_ID"] == null ? 0 : decimal.Parse(row["N_CUSTOMER_ID"].ToString());
                entity.C_CUSTOMER_NAME = row["C_CUSTOMER_NAME"].ToString();
                entity.C_USER_TYPE = row["C_USER_TYPE"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == null ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == null ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == null ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                UpdateOrCreateRange3(applyId, proId, 3, entitys);
            }
        }

        public bool DeleteByApplyId(decimal applyId, decimal proId, int N_TYPE, string userid = null)
        {
            var sql = "DELETE QS_PLAN_DETAIL2 WHERE N_PLANAPP_ID=" + applyId + " AND N_PRO_ID=" + proId + @" AND N_TYPE=" + N_TYPE;

            if (!string.IsNullOrEmpty(userid))
            {
                sql += " AND N_USERID = " + userid;
            }

            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert(QS_PLAN_DETAIL2 entity, string userid = "null")
        {
            var seq = GetSequence("SEQ_N_ID2");
            entity.N_ID2 = seq;
            var sql = @"insert into qs_plan_detail2
  (n_id2,
   n_pro_id,
   n_type,
   n_planapp_id,
   n_customer_id,
   c_customer_name,
   c_user_type,
   n_dosage,
   n_price,
   n_amount,
   c_bank,
   c_remark,
   n_num,
   n_userid)
values
  (" + entity.N_ID2 + @",
   " + entity.N_PRO_ID + @",
   " + entity.N_TYPE + @",
     " + entity.N_PLANAPP_ID + @",
   " + entity.N_CUSTOMER_ID + @",
    '" + entity.C_CUSTOMER_NAME + @"',
 '" + entity.C_USER_TYPE + @"',
  " + entity.N_DOSAGE + @",
  " + entity.N_PRICE + @",
  " + entity.N_AMOUNT + @",
  '" + entity.C_BANK + @"',
  '" + entity.C_REMARK + @"',
   " + entity.N_NUM + @",
    " + userid + @")";

            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId">申请id</param>
        /// <param name="proId">项目id</param>
        /// <param name="type">类型：1 年资金,2 月资金,3 周资金</param>
        /// <param name="data"></param>
        public void UpdateOrCreateRange3(int applyId, int proId, int type, List<QS_PLAN_DETAIL2> data)
        {
            //先清空再添加
            if (type == 4 || type == 5)
            {
                DeleteByApplyId(applyId, proId, type, base.User.USER_ID.ToString());
            }
            else
            {
                DeleteByApplyId(applyId, proId, type);
            }
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAIL2();
                entity.N_PRO_ID = item.N_PRO_ID;
                entity.N_TYPE = item.N_TYPE;
                entity.N_PLANAPP_ID = item.N_PLANAPP_ID;

                entity.N_CUSTOMER_ID = item.N_CUSTOMER_ID;
                entity.C_CUSTOMER_NAME = item.C_CUSTOMER_NAME;

                entity.C_USER_TYPE = item.C_USER_TYPE;
                entity.N_DOSAGE = item.N_DOSAGE;
                entity.N_PRICE = item.N_PRICE;
                entity.N_AMOUNT = item.N_AMOUNT;

                entity.C_BANK = item.C_BANK;
                entity.C_REMARK = item.C_REMARK;
                entity.N_NUM = item.N_NUM;

                if (Insert(entity, base.User.USER_ID.ToString()))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"update qs_planmonth_detail t
   set t.n_amount =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail2 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
 )
 where t.n_monthapp_id = " + applyId + @"
   and t.n_proid =" + proId;

            if (type == 3)
            {
                //周计划明细
                sql = @"update QS_PLANWEEK_DETAIL t
   set t.n_amount =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail2 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
 )
 where t.N_WEEKAPP_ID = " + applyId + @"
   and t.n_proid =" + proId;
            }

            if (type == 4)
            {
                //月部门详情
                sql = @"update QS_PLANMONTH_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail2 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_userid = " + base.User.USER_ID + @"
 )
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID = " + base.User.USER_ID;
            }

            if (type == 5)
            {
                //周部门详情
                sql = @"update QS_PLANWEEK_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail2 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_userid = " + base.User.USER_ID + @"
 )
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID = " + base.User.USER_ID;
            }

            var dt = NoQuery(sql);
        }

        public DataTable ExcelData(int applyId, int proId, int type)
        {
            string sql = @"select decode(序号, 999999, null, 序号) 序号,
       单位名称,
       用户类别,
       预计用气量,
       单价,
       预计气款金额,
       银行现金流向,
       备注
  from (select t.n_num 序号,
               t.c_customer_name 单位名称,
               t.c_user_type 用户类别,
               decode(t.n_dosage, 0, null, t.n_dosage) 预计用气量,
               decode(t.n_price, 0, '', t.n_price) 单价,
               decode(t.n_amount, 0, null, t.n_amount) 预计气款金额,
               t.c_bank 银行现金流向,
               t.c_remark 备注
          from QS_PLAN_DETAIL2 t
         WHERE t.n_type = " + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id = " + applyId + @"
        --order by N_NUM, N_ID2
        union all
        select 999999,
               '合计',
               '',
               to_char(sum(t.n_dosage)),
               null,
               to_char(sum(t.n_amount)),
               '',
               ''
          from QS_PLAN_DETAIL2 t
         WHERE t.n_type = " + type + @"
           and t.n_pro_id = " + proId + @"
           and t.n_planapp_id =  " + applyId + @")
 order by 序号";

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetTable(int appId, int type)
        {
            switch (type)
            {
                case 3:
                    return GetTableByWeek(appId);

                case 2:
                    return GetTableByMonth(appId);

                case 1:
                    return GetTableByYear(appId);

                default:
                    break;
            }
            return null;
        }

        /// <summary>
        /// 查询天然气统计（周）
        /// </summary>
        /// <param name="planappId"></param>
        /// <returns></returns>
        public DataTable GetTableByWeek(int applyId)
        {
            var sql1 = @"SELECT
                            ROW_NUMBER () OVER (ORDER BY T2.C_CUSTOMER_NAME) AS R,
	                        T2.N_ID2,
	                        T2.N_PRO_ID,
	                        T2.N_PLANAPP_ID,
                            T2.C_CUSTOMER_NAME,
		                    T2.C_USER_TYPE,
                            CASE
                              WHEN T2.N_AMOUNT IS NULL THEN 0
                              ELSE T2.N_AMOUNT
                            END AS N_AMOUNT,
                            CASE
                              WHEN T2.N_USEAMOUNT IS NULL THEN 0
                              ELSE T2.N_USEAMOUNT
                            END AS N_USEAMOUNT,
	                        CASE
		                        WHEN T2.N_AMOUNT=0 THEN '0%'
		                        WHEN T2.N_AMOUNT IS NULL THEN '0%'
		                        WHEN T2.N_USEAMOUNT=0 THEN '0%'
		                        WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
		                        ELSE TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_AMOUNT*100,6))||'%'
	                        END AS N_AMOUNTPROBABILITY,
                            T2.C_REMARK
                          FROM QS_PLANWEEKAPPLY T1
                          INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
                          WHERE
                            T1.N_BUSINESS_STATE = 2 AND
                            T1.N_WEEKAPP_ID =:WEEKAPP_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":WEEKAPP_ID", applyId, "int");
            DataTable dt = Query(sql1, parms);
            return dt;
        }

        /// <summary>
        /// 查询天然气统计（月）
        /// </summary>
        /// <param name="planappId"></param>
        /// <returns></returns>
        public DataTable GetTableByMonth(int applyId)
        {
            var sql1 = @"SELECT
	ROW_NUMBER () OVER (ORDER BY T2.C_CUSTOMER_NAME) AS R,
	CASE
WHEN T1.C_CUSTOMER_NAME IS NULL THEN
	T2.C_CUSTOMER_NAME
ELSE
	T1.C_CUSTOMER_NAME
END AS C_CUSTOMER_NAME,
 CASE
WHEN T1.C_USER_TYPE IS NULL THEN
	T2.C_USER_TYPE
ELSE
	T1.C_USER_TYPE
END AS C_USER_TYPE,
 T2.N_AMOUNT AS N_WEEK_AMOUNT,
 T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
 T2.N_GOL,
 T2.N_ID2,
 T2.N_DOSAGE,
 T2.N_PRICE,
 T2.N_AMOUNT,
 T2.C_REMARK,
 T2.N_PRO_ID,
 T2.N_PLANAPP_ID
FROM
	(
		SELECT
			T1.*
		FROM
			QS_PLAN_DETAIL2 T1
		WHERE
			T1.N_TYPE = 2 AND
			T1.N_PLANAPP_ID =:APPLY_ID
	) T1
FULL JOIN (
	SELECT
		T1.*, CASE
	WHEN T1.N_AMOUNT IS NULL THEN
		'0%'
	WHEN T1.N_AMOUNT = 0 THEN
		'0%'
	WHEN T1.N_USEAMOUNT IS NULL THEN
		'0%'
	WHEN T1.N_USEAMOUNT = 0 THEN
		'0%'
	ELSE
		TO_CHAR (
			ROUND (
				T1.N_USEAMOUNT / T1.N_AMOUNT * 100,
				2
			)
		) || '%'
	END AS N_GOL
	FROM
		(
			SELECT
				T2.C_CUSTOMER_NAME,
				T2.C_USER_TYPE,
				SUM (
					CASE
					WHEN T2.N_AMOUNT IS NULL THEN
						0
					ELSE
						T2.N_AMOUNT
					END
				) AS N_AMOUNT,
				SUM (
					CASE
					WHEN T2.N_USEAMOUNT IS NULL THEN
						0
					ELSE
						T2.N_USEAMOUNT
					END
				) AS N_USEAMOUNT,
                 T2.N_ID2,
                 T2.N_DOSAGE,
                 T2.N_PRICE,
                 T2.C_REMARK,
                 T2.N_PRO_ID,
                 T2.N_PLANAPP_ID
			FROM
				QS_PLANWEEKAPPLY T1
			INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID
			AND T2.N_TYPE = 3
			INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
			WHERE
				T1.N_BUSINESS_STATE = 2 AND
			    T1.N_MONTHAPP_ID =:APPLY_ID
			GROUP BY
				T2.C_CUSTOMER_NAME,
				T2.C_USER_TYPE,
                 T2.N_ID2,
                 T2.N_DOSAGE,
                 T2.N_PRICE,
                 T2.C_REMARK,
                 T2.N_PRO_ID,
                 T2.N_PLANAPP_ID
		) T1
) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME
AND T1.C_USER_TYPE = T2.C_USER_TYPE";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            DataTable dt = Query(sql1, parms);
            return dt;
        }

        /// <summary>
        /// 查询天然气统计（年）
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTableByYear(int applyId)
        {
            var sql = @"SELECT
	ROW_NUMBER () OVER (ORDER BY T2.C_CUSTOMER_NAME) AS R,
	CASE
WHEN T1.C_CUSTOMER_NAME IS NULL THEN
	T2.C_CUSTOMER_NAME
ELSE
	T1.C_CUSTOMER_NAME
END AS C_CUSTOMER_NAME,
 CASE
WHEN T1.C_USER_TYPE IS NULL THEN
	T2.C_USER_TYPE
ELSE
	T1.C_USER_TYPE
END AS C_USER_TYPE,
 T2.N_AMOUNT AS N_WEEK_AMOUNT,
 T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
 T2.N_GOL,
 /*T1.N_ID2,
 T1.N_DOSAGE,
 T1.N_PRICE,
 T1.C_REMARK,*/
 T2.N_AMOUNT,T2.N_ID2,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_DOSAGE,T2.N_PRICE,T2.C_REMARK
FROM
	(
		SELECT
			T2.*
		FROM
			QX_PLANYEARAPPLY T1
		INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_YEARAPP_ID = T2.N_PLANAPP_ID
		AND T2.N_TYPE = 1
		WHERE
			T2.N_PLANAPP_ID =:APPLY_ID
	) T1
FULL JOIN (
	SELECT
		T1.*, CASE
	WHEN T1.N_AMOUNT IS NULL THEN
		'0%'
	WHEN T1.N_AMOUNT = 0 THEN
		'0%'
	WHEN T1.N_USEAMOUNT IS NULL THEN
		'0%'
	WHEN T1.N_USEAMOUNT = 0 THEN
		'0%'
	ELSE
		TO_CHAR (
			ROUND (
				T1.N_USEAMOUNT / T1.N_AMOUNT * 100,
				2
			)
		) || '%'
	END AS N_GOL
	FROM
		(
			SELECT
				T2.C_CUSTOMER_NAME,
				T2.C_USER_TYPE,
				SUM (
					CASE
					WHEN T2.N_AMOUNT IS NULL THEN
						0
					ELSE
						T2.N_AMOUNT
					END
				) AS N_AMOUNT,
				SUM (
					CASE
					WHEN T2.N_USEAMOUNT IS NULL THEN
						0
					ELSE
						T2.N_USEAMOUNT
					END
				) AS N_USEAMOUNT,T2.N_ID2,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_DOSAGE,T2.N_PRICE,T2.C_REMARK
			FROM
				QS_PLANWEEKAPPLY T1
			INNER JOIN QS_PLAN_DETAIL2 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID
			AND T2.N_TYPE = 3
			INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
            INNER JOIN QX_PLANYEARAPPLY T4 ON T3.N_YEARAPP_ID = T4.N_YEARAPP_ID
			WHERE
				T1.N_BUSINESS_STATE = 2 AND
                T4.N_BUSINESS_STATE = 2 AND
                T3.N_BUSINESS_STATE = 2 AND
                T4.N_YEARAPP_ID =:APPLY_ID
			GROUP BY
				T2.C_CUSTOMER_NAME,
				T2.C_USER_TYPE,T2.N_ID2,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_DOSAGE,T2.N_PRICE,T2.C_REMARK
		) T1
) T2 ON T1.C_CUSTOMER_NAME = T2.C_CUSTOMER_NAME
AND T1.C_USER_TYPE = T2.C_USER_TYPE";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }
    }
}